from openpyxl import Workbook
from openpyxl.comments import Comment
import openpyxl
def test1():
	wb = Workbook()
	ws = wb.active
	ws["A1"].value = 'hello, world'
	# comment = ws["A1"].comment
	comment=Comment('This is the comment text', 'Comment Author')
	comment.width = 300
	comment.height = 50
	ws["A1"].comment = comment
	wb.save("test.xlsx")
def test2():
	xl_book=openpyxl.load_workbook(filename="test.xlsx")
	table = xl_book.worksheets[0]
	comment = table["A1"].comment
	print(comment.text)
	print(comment.author)
def chart1():
	from openpyxl import Workbook
	from openpyxl.chart import (
	    AreaChart,
	    Reference,
	    Series,
	)

	wb = Workbook()
	ws = wb.active

	rows = [
	    ['Number', 'Batch 1', 'Batch 2'],
	    [2, 40, 30],
	    [3, 40, 25],
	    [4, 50, 30],
	    [5, 30, 10],
	    [6, 25, 5],
	    [7, 50, 10],
	]

	for row in rows:
	    ws.append(row)

	chart = AreaChart()
	chart.title = "Area Chart"
	chart.style = 13
	chart.x_axis.title = 'Test'
	chart.y_axis.title = 'Percentage'

	cats = Reference(ws, min_col=1, min_row=1, max_row=7)
	data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
	chart.add_data(data, titles_from_data=True)
	chart.set_categories(cats)

	ws.add_chart(chart, "A10")

	wb.save("area.xlsx")
def chart2():
	from openpyxl import Workbook
	from openpyxl.chart import BarChart, Series, Reference

	wb = Workbook(write_only=True)
	ws = wb.create_sheet()

	rows = [
	    ('Number', 'Batch 1', 'Batch 2'),
	    (2, 10, 30),
	    (3, 40, 60),
	    (4, 50, 70),
	    (5, 20, 10),
	    (6, 10, 40),
	    (7, 50, 30),
	]


	for row in rows:
	    ws.append(row)


	chart1 = BarChart()
	chart1.type = "col"
	chart1.style = 10
	chart1.title = "Bar Chart"
	chart1.y_axis.title = 'Test number'
	chart1.x_axis.title = 'Sample length (mm)'

	data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
	cats = Reference(ws, min_col=1, min_row=2, max_row=7)
	chart1.add_data(data, titles_from_data=True)
	chart1.set_categories(cats)
	chart1.shape = 4
	ws.add_chart(chart1, "A10")

	from copy import deepcopy

	chart2 = deepcopy(chart1)
	chart2.style = 11
	chart2.type = "bar"
	chart2.title = "Horizontal Bar Chart"

	ws.add_chart(chart2, "G10")


	chart3 = deepcopy(chart1)
	chart3.type = "col"
	chart3.style = 12
	chart3.grouping = "stacked"
	chart3.overlap = 100
	chart3.title = 'Stacked Chart'

	ws.add_chart(chart3, "A27")


	chart4 = deepcopy(chart1)
	chart4.type = "bar"
	chart4.style = 13
	chart4.grouping = "percentStacked"
	chart4.overlap = 100
	chart4.title = 'Percent Stacked Chart'

	ws.add_chart(chart4, "G27")

	wb.save("bar.xlsx")	
def filter():
	from openpyxl import Workbook
	from openpyxl.worksheet.filters import (
	    FilterColumn,
	    CustomFilter,
	    CustomFilters,
	    DateGroupItem,
	    Filters,
	    )

	wb = Workbook()
	ws = wb.active

	data = [
	    ["Fruit", "Quantity"],
	    ["Kiwi", 3],
	    ["Grape", 15],
	    ["Apple", 3],
	    ["Peach", 3],
	    ["Pomegranate", 3],
	    ["Pear", 3],
	    ["Tangerine", 3],
	    ["Blueberry", 3],
	    ["Mango", 3],
	    ["Watermelon", 3],
	    ["Blackberry", 3],
	    ["Orange", 3],
	    ["Raspberry", 3],
	    ["Banana", 3]
	]

	for r in data:
	    ws.append(r)

	filters = ws.auto_filter
	filters.ref = "A1:B15"
	col = FilterColumn(colId=0) # for column A
	col.filters = Filters(filter=["Kiwi", "Apple", "Mango"]) # add selected values
	filters.filterColumn.append(col) # add filter to the worksheet

	ws.auto_filter.add_sort_condition("B2:B15")

	wb.save("filtered.xlsx")
def w1():
	import xlsxwriter

	workbook = xlsxwriter.Workbook('hello.xlsx')
	worksheet = workbook.add_worksheet()

	worksheet.write('A1', 'Hello world')

	workbook.close()
def com2():
	import xlsxwriter

	wb = xlsxwriter.Workbook('hello.xlsx')
	ws = wb.add_worksheet()

	data='XlsxWriter Library'

	ws.set_column('C:C', 25)
	ws.set_row(2, 50)
	ws.write('C3', data)

	text = 'Developed by John McNamara'
	ws.write_comment('C3', text)

	wb.close()
def img1():
	from io import BytesIO
	import xlsxwriter
	workbook = xlsxwriter.Workbook('hello.xlsx')
	worksheet = workbook.add_worksheet()

	filename = 'images/1_Si.jpg'#star.png'

	file = open(filename, 'rb')
	data = BytesIO(file.read())
	file.close()

	worksheet.insert_image('C5', filename, {'image_data': data})

	workbook.close()		
# test1()
# test2()
# chart1()
# filter()
# chart2()
# w1()
# com2()
img1()